package com.bringway.p2p.dao.impl;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.bringway.framework.dao.P2PJdbcDao;
import com.bringway.p2p.dao.GrsyDao;


/**
 * 
 * @author zhai
 * @version 创建时间：2015年12月16日 上午9:34:42
 * @功能：个人收益详情界面
 */

@Repository
public class GrsyDaoImpl  extends P2PJdbcDao implements GrsyDao{
	
	Logger logger=Logger.getLogger(GrsyDaoImpl.class);

	@Override
	public List queryGrsyList(JSONObject params) {

		int start = params.optInt("start");
		int limit = params.optInt("limit");
		String minczje = params.optString("minczje");
		String maxczje = params.optString("maxczje");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username= params.optString("username");
		String sjhm= params.optString("sjhm");
		String type= params.optString("type");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("START", start);
		paramMap.put("LIMIT", limit);
		sql.append(" SELECT t1.ID, t1.USERNAME, t1.ZC_CHANGE_TYPE,                                       ");
		sql.append(" 	date_format(t1.ZC_CHANGE_DATE,'%Y-%m-%d %H:%i:%s') as ZC_CHANGE_DATE             ");
		sql.append("     ,t1.ZC_CHANGE_JE, t1.ZC_OCCUR_JE, t1.ZC_ORIGINAL_DJJE, t1.ZC_ORIGINAL_KYJE, t1.ZC_ORIGINAL_ZHYE ");
		sql.append("     ,t1.ZC_FINAL_KYJE, t1.ZC_FINAL_DJJE, t1.ZC_FINAL_ZHYE                           ");
		sql.append("     ,t2.SJHM, t2.TJR                                                                ");
		sql.append("     FROM v_p2p_zc_details t1                                             ");
		sql.append(" left join v_p2p_user_tjr t2 on  t1.USERNAME=t2.USER_NAME");
		sql.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND t1.ZC_CHANGE_DATE >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND t1.ZC_CHANGE_DATE <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+"'%"+username+"%' ");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+"'%"+sjhm+"%' ");
		}
		if(StringUtils.isNotEmpty(minczje)){
	        sql.append(" AND ZC_CHANGE_JE >= :MINCZJE ");
	        paramMap.put("MINCZJE", minczje);
		}
		
	    if(StringUtils.isNotEmpty(maxczje)){
			sql.append(" AND ZC_CHANGE_JE <= :MAXCZJE");
			paramMap.put("MAXCZJE", maxczje);
		}
	    if((StringUtils.isNotEmpty(type))){
			sql.append(" AND ZC_CHANGE_TYPE =:type");
			paramMap.put("type", type);
		}
		logger.info("queryGrsyList sql is "+sql.toString());
		logger.info("queryGrsyList paramMap is "+paramMap);
		sql.append(" order by t1.ZC_CHANGE_DATE desc LIMIT :START,:LIMIT");
		return this.getNamedParameterJdbcTemplate().queryForList(
				sql.toString(), paramMap);

	}

	/**
	 * @author zhai
	 * 获取个人收益数据的个数
	 */
	@Override
	public int getGrsyCount(JSONObject params) {
		String minczje = params.optString("minczje");
		String maxczje = params.optString("maxczje");
		String startDate = params.optString("startDate");
		String endDate = params.optString("endDate");
		if(StringUtils.isNotEmpty(startDate)){
			startDate=startDate+" 00:00:00";
		}
		if(StringUtils.isNotEmpty(endDate)){
			endDate=endDate+" 23:59:59";
		}
		String username= params.optString("username");
		String sjhm= params.optString("sjhm");
		String type= params.optString("type");
		StringBuffer sql = new StringBuffer();
		Map<String, Object> paramMap = new HashMap<String, Object>();
		sql.append(" select count(*) from v_p2p_zc_details t1");
		sql.append(" left join v_p2p_user_tjr t2 on  t1.USERNAME=t2.USER_NAME");
		sql.append(" where 1=1 ");
		if(StringUtils.isNotEmpty(startDate)){
			sql.append(" AND ZC_CHANGE_DATE >= :startDate ");
			paramMap.put("startDate",startDate);
		}
		if(StringUtils.isNotEmpty(endDate)){
			sql.append(" AND ZC_CHANGE_DATE <= :endDate ");
			paramMap.put("endDate",endDate);
		}
		
		if(StringUtils.isNotEmpty(username)){
			sql.append(" AND USERNAME like "+"'%"+username+"%' ");
		}
		if(StringUtils.isNotEmpty(sjhm)){
			sql.append(" AND SJHM like "+"'%"+sjhm+"%' ");
		}
		if(StringUtils.isNotEmpty(minczje)){
	        sql.append(" AND ZC_CHANGE_JE >= :MINCZJE ");
	        paramMap.put("MINCZJE", minczje);
		}
		
	    if(StringUtils.isNotEmpty(maxczje)){
			sql.append(" AND ZC_CHANGE_JE <= :MAXCZJE");
			paramMap.put("MAXCZJE", maxczje);
		}
	    if((StringUtils.isNotEmpty(type))){
			sql.append(" AND ZC_CHANGE_TYPE =:type");
			paramMap.put("type", type);
		}
		logger.info("getGrsyCount sql is "+sql.toString());
		logger.info("getGrsyCount paramMap is "+paramMap);
		return this.getNamedParameterJdbcTemplate().queryForInt(sql.toString(), paramMap);
	}



}
